﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.IO;
using csRebides;
using Mono.Data.Sqlite;  

namespace csRebides
{
    class functions
    {

        subFunction sF;

        public functions()
        {
            sF = new subFunction();
        }

        /*
         * »»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»
         * here start the methods of this class
         * »»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»»
        */

        /*
         * ##################################################################
         * Function to get the statistic with the number of teachers per year
         * ##################################################################
        */
        public Dictionary<string, int> statTeachY()
        {
            Dictionary<string, int> list = new Dictionary<string, int>();
            Dictionary<string, int> sortedList = new Dictionary<string, int>();

            string connectionString = "URI=file:database/pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();

            for (int i = 0; i < 10; i++)
            {
                string year = i.ToString();
                string sql =
                    "SELECT COUNT(DISTINCT docente_id) " +
                    "FROM informatica_registodocencia " +
                    "WHERE informatica_registodocencia.ano = " + year;
                dbcmd.CommandText = sql;
                IDataReader reader = dbcmd.ExecuteReader();

                while (reader.Read())
                {
                    Int32 value = reader.GetInt32(0);
                    Console.WriteLine("Year 200" + year + ": " + value);
                    list.Add("Year 200" + year + ": ", value);
                }

                reader.Close();
                reader = null;
            }

            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;

            var items = from k in list.Keys
                        orderby list[k] ascending
                        select k;

            foreach (string k in items)
            {
                sortedList.Add(k, list[k]);
            }

            return sortedList;
        } //end of statTeachY



        /*
         * ####################################################################################
         * Function to get the statistic with the number of teachers per establisment per year
         * ####################################################################################
        */
        public Dictionary<string, Dictionary<string, int>> statTeachEstY()
        {
            Dictionary<string, Dictionary<string, int>> finalList = new Dictionary<string, Dictionary<string, int>>();

            string connectionString = "URI=file:database/pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();

            for (int i = 0; i < 10; i++)
            {
                Console.WriteLine("Year 200{0} #######################", i);
                Dictionary<string, int> estatTEY = new Dictionary<string, int>();
                Dictionary<string, int> sortedEstatTEY = new Dictionary<string, int>();
                string year = i.ToString();
                string sql = "SELECT informatica_estabelecimento.designacao, COUNT(DISTINCT docente_id) AS TotDocentes " +
                              "FROM informatica_registodocencia " +
                              "INNER JOIN informatica_estabelecimento on " +
                              "informatica_registodocencia.estabelecimento_id = informatica_estabelecimento.id " +
                              "WHERE informatica_registodocencia.ano = " + year + " " +
                              "GROUP BY informatica_estabelecimento.designacao";
                dbcmd.CommandText = sql;
                IDataReader reader = dbcmd.ExecuteReader();
                
                while (reader.Read())
                {
                    string estab = reader.GetString(0);
                    Int32 value = reader.GetInt32(1);
                    Console.WriteLine(estab + ": " + value);
                    estatTEY.Add(estab + ": ", value);
                }

                reader.Close();
                reader = null;


                var items = from k in estatTEY.Keys
                            orderby estatTEY[k] ascending
                            select k;

                foreach (string estab in items)
                {
                    sortedEstatTEY.Add(estab, estatTEY[estab]);
                    Console.WriteLine(estab + sortedEstatTEY[estab]);
                }

                finalList.Add("Year 200" + i, sortedEstatTEY);
                Console.WriteLine("Year 200{0} OK #######################", i);
                Console.WriteLine();
            }

            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;
            return finalList;
        } //end of statTeachEstY



        /*
         * #################################################################################
         * Function to get the statistic with the number of teachers per degree per year
         * #################################################################################
        */
        public Dictionary<string, Dictionary<string, int>> statTeachDegY()
        {
            Dictionary<string, Dictionary<string, int>> finalList = new Dictionary<string, Dictionary<string, int>>();

            string connectionString = "URI=file:database/pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();

            for (int i = 0; i < 10; i++)
            {
                Console.WriteLine("Year 200{0} #######################", i);
                Dictionary<string, int> estatTDY = new Dictionary<string, int>();
                Dictionary<string, int> sortedEstatTDY = new Dictionary<string, int>();
                string year = i.ToString();
                string sql = "SELECT informatica_grau.designacao, COUNT(DISTINCT docente_id) AS TotDocentes " +
                              "FROM informatica_registodocencia " +
                              "INNER JOIN informatica_grau on " +
                              "informatica_registodocencia.grau_id = informatica_grau.id " +
                              "WHERE informatica_registodocencia.ano = " + year + " " +
                              "GROUP BY informatica_grau.designacao";
                dbcmd.CommandText = sql;
                IDataReader reader = dbcmd.ExecuteReader();
                
                while (reader.Read())
                {
                    string degree = reader.GetString(0);
                    Int32 value = reader.GetInt32(1);
                    Console.WriteLine(degree + ": " + value);
                    estatTDY.Add(degree + ": ", value);
                }

                reader.Close();
                reader = null;

                var items = from k in estatTDY.Keys
                            orderby estatTDY[k] ascending
                            select k;

                foreach (string degree in items)
                {
                    sortedEstatTDY.Add(degree, estatTDY[degree]);
                    Console.WriteLine(degree + sortedEstatTDY[degree]);
                }

                finalList.Add("Year 200" + i, sortedEstatTDY);
                Console.WriteLine("Year 200{0} OK #######################", i);
                Console.WriteLine();
            }

            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;
            return finalList;
        } //end of statTeachDegY



        /*
         * ###################################################################################################
         * Function to get the statistic with the number of teachers per degree and per establishment per year
         * ###################################################################################################
        */
        public Dictionary<string, Dictionary<string, Dictionary<string, int>>> statTeachDegEstY()
        {
            Dictionary<string, Dictionary<string, Dictionary<string, int>>> finalList = new Dictionary<string, Dictionary<string, Dictionary<string, int>>>();

            string connectionString = "URI=file:database/pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();

            for (int i = 0; i < 10; i++)
            {
                List<string> degrees = sF.listDeg(i);
                Dictionary<string, Dictionary<string, int>> midleList = new Dictionary<string, Dictionary<string, int>>();

                foreach (string deg in degrees)
                {
                    string degree = '"' + deg + '"';
                    Console.WriteLine();
                    Console.WriteLine("Year 200{0} #######################", i);
                    Console.WriteLine(degree);
                    Console.WriteLine();
                    Dictionary<string, int> estatTDEY = new Dictionary<string, int>();
                    Dictionary<string, int> sortedEstatTDEY = new Dictionary<string, int>();
                    string year = i.ToString();
                    string sql =
                                "SELECT informatica_estabelecimento.designacao, COUNT(DISTINCT docente_id) AS TotDocente " +
                                "FROM informatica_registodocencia " +
                                "INNER JOIN informatica_grau on " +
                                "informatica_registodocencia.grau_id = informatica_grau.id " +
                                "INNER JOIN informatica_estabelecimento on " +
                                "informatica_registodocencia.estabelecimento_id = informatica_estabelecimento.id " +
                                 "WHERE informatica_registodocencia.ano=" + year + " and informatica_grau.designacao = " + degree + " " +
                                 "GROUP BY informatica_estabelecimento.designacao";
                    dbcmd.CommandText = sql;
                    IDataReader reader = dbcmd.ExecuteReader();
                    
                    while (reader.Read())
                    {
                        string estab = reader.GetString(0);
                        Int32 value = reader.GetInt32(1);
                        estatTDEY.Add(estab + ": ", value);
                        Console.WriteLine(estab + ": " + value);
                    }

                    var items = from k in estatTDEY.Keys
                                orderby estatTDEY[k] ascending
                                select k;

                    foreach (string estab in items)
                    {
                        sortedEstatTDEY.Add(estab, estatTDEY[estab]);
                        Console.WriteLine(estab + sortedEstatTDEY[estab]);
                    }

                    midleList.Add(degree, sortedEstatTDEY);
                    reader.Close();
                    reader = null;
                }

                finalList.Add("Year 200" + i, midleList);
                Console.WriteLine("Year 200{0} OK #######################", i);
                Console.WriteLine();
            }

            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;
            return finalList;
        } //end of statTeachDegEstY



        /*
         * ###########################################################
         * Function to get the statistic with the number of teachers 
         * with doctorate degree per establishment per year
         * ###########################################################
        */
        public Dictionary<string, Dictionary<string, Dictionary<string, int>>> statTeachDocDegEstY()
        {
            Dictionary<string, Dictionary<string, Dictionary<string, int>>> finalList = new Dictionary<string, Dictionary<string, Dictionary<string, int>>>();

            string connectionString = "URI=file:database/pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();

            for (int i = 0; i < 10; i++)
            {
                List<string> docDegrees = sF.listDegByName(i, "Do%");
                Dictionary<string, Dictionary<string, int>> midleList = new Dictionary<string, Dictionary<string, int>>();
                Console.WriteLine();

                foreach (string docDeg in docDegrees)
                {
                    string degree = '"' + docDeg + '"';
                    Dictionary<string, int> estatTDocDEY = new Dictionary<string, int>();
                    Dictionary<string, int> sortedestatTDocDEY = new Dictionary<string, int>();
                    string year = i.ToString();
                    Console.WriteLine();
                    Console.WriteLine("Year 200{0} #######################", i);
                    Console.WriteLine(degree);
                    Console.WriteLine();
                    string sql =
                                "SELECT informatica_estabelecimento.designacao, COUNT(DISTINCT docente_id) AS TotDocente " +
                                "FROM informatica_registodocencia " +
                                "INNER JOIN informatica_grau on " +
                                "informatica_registodocencia.grau_id = informatica_grau.id " +
                                "INNER JOIN informatica_estabelecimento on " +
                                "informatica_registodocencia.estabelecimento_id = informatica_estabelecimento.id " +
                                 "WHERE informatica_registodocencia.ano = " + year + " and informatica_grau.designacao like " + degree + " " +
                                 "GROUP BY informatica_estabelecimento.designacao";
                    dbcmd.CommandText = sql;
                    IDataReader reader = dbcmd.ExecuteReader();

                    while (reader.Read())
                    {
                        string estab = reader.GetString(0);
                        Int32 value = reader.GetInt32(1);
                        estatTDocDEY.Add(estab + ": ", value);
                        Console.WriteLine(estab + ": " + value);
                    }

                    var items = from k in estatTDocDEY.Keys
                                orderby estatTDocDEY[k] ascending
                                select k;

                    foreach (string estab in items)
                    {
                        sortedestatTDocDEY.Add(estab, estatTDocDEY[estab]);
                        Console.WriteLine(estab + sortedestatTDocDEY[estab]);
                    }

                    midleList.Add(degree, sortedestatTDocDEY);
                    reader.Close();
                    reader = null;
                }

                finalList.Add("Year 200" + i, midleList);
                Console.WriteLine("Year 200{0} OK #######################", i);
                Console.WriteLine();
            }

            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;
            return finalList;
        } //end of statTeachDocDegEstY



        /*
         * ###########################################################
         * Function to get the statistic with the set of teachers 
         * with doctorate degree per establishment per year
         * ###########################################################
        */
        public Dictionary<string, Dictionary<string, List<string>>> statSetTeachDocDegEstY()
        {
            Dictionary<string, Dictionary<string, List<string>>> finalList = new Dictionary<string, Dictionary<string, List<string>>>();

            string connectionString = "URI=file:database/pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();

            for (int i = 0; i < 10; i++)
            {
                string docDegrees = "Do%";

                List<string> listEstByDeg = sF.listEstByDegName(i, docDegrees);
                Dictionary<string, List<string>> estatSetTDocEY = new Dictionary<string, List<string>>();

                foreach (string estByDeg in listEstByDeg)
                {
                    List<string> docNames = new List<string>();
                    string estab = '"' + estByDeg + '"';
                    string deg = '"' + docDegrees + '"';
                    string year = i.ToString();
                    Console.WriteLine();
                    Console.WriteLine("Year 200{0} #######################", i);
                    Console.WriteLine(estab);
                    Console.WriteLine();
                    string sql =
                                "SELECT DISTINCT informatica_docente.nome " +
                                "FROM informatica_registodocencia " +
                                "INNER JOIN informatica_grau on " +
                                "informatica_registodocencia.grau_id = informatica_grau.id " +
                                "INNER JOIN informatica_docente on " +
                                "informatica_registodocencia.docente_id = informatica_docente.id " +
                                "INNER JOIN informatica_estabelecimento on " +
                                "informatica_registodocencia.estabelecimento_id = informatica_estabelecimento.id " +
                                 "WHERE informatica_registodocencia.ano = " + year + " and informatica_grau.designacao like " + deg +
                                 " and informatica_estabelecimento.designacao like " + estab + " " +
                                 "GROUP BY informatica_docente.nome";

                    dbcmd.CommandText = sql;
                    IDataReader reader = dbcmd.ExecuteReader();

                    while (reader.Read())
                    {
                        string docentName = reader.GetString(0);
                        docNames.Add(docentName);
                        Console.WriteLine(docentName);
                    }

                    docNames.Sort();
                    estatSetTDocEY.Add(estByDeg, docNames);
                    reader.Close();
                    reader = null;

                }

                finalList.Add("Year 200" + i, estatSetTDocEY);
                Console.WriteLine("Year 200{0} OK #######################", i);
                Console.WriteLine();
            }
            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;
            return finalList;
        } //end of statSetTeachDocDegEstY



        /*
         * ###########################################################
         * Function to get the statistic with the set of teachers 
         * that changed from one establishment to another per year
         * ###########################################################
        */
        public Dictionary<string, Dictionary<List<string>, int>> statSetTeachChangeEstY()
        {
            Dictionary<string, Dictionary<List<string>, int>> finalList = new Dictionary<string, Dictionary<List<string>, int>>();

            for (int i = 0; i < 9; i++)
            {
                Console.WriteLine("Year 200{0} #######################", (i + 1));
                List<string> changeListY = new List<string>();
                Dictionary<List<string>, int> midleList = new Dictionary<List<string>, int>();
                List<MyDict> listAfter = sF.listTeachEstIdY(i + 1);
                List<MyDict> list = sF.listTeachEstIdY(i);
                var result = listAfter.Except(list);
                List<string> resultNames = new List<string>();
                List<string> listNames = new List<string>();
                int percentCount = result.Count() / 10;

                foreach (MyDict name in list)
                {
                    listNames.Add(name.key);
                }

                foreach (MyDict name in result)
                {
                    resultNames.Add(name.key);
                }

                var result1 = resultNames.Intersect(listNames);

                foreach (string name in result1)
                {
                    changeListY.Add(name);
                }

                changeListY.Sort();

                int total = changeListY.Count();

                midleList.Add(changeListY, total);

                finalList.Add("Year 200" + (i + 1), midleList);
                Console.WriteLine("Year 200{0} OK #######################", (i + 1));
                Console.WriteLine();
            }

            return finalList;
        } //end of statSetTeachDocDegEstY



        /*
         * ###################################################################################
         * Function to get the list of the establishments by year
         * ###################################################################################
        */
        public Dictionary<string, List<string>> listEstabY()
        {
            Dictionary<string, List<string>> finalList = new Dictionary<string, List<string>>();

            string connectionString = "URI=file:database/pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();

            for (int i = 0; i < 10; i++)
            {
                List<string> list = new List<string>();
                string year = i.ToString();
                string sql =
                            "SELECT DISTINCT informatica_estabelecimento.designacao " +
                            "FROM informatica_registodocencia " +
                            "INNER JOIN informatica_estabelecimento on " +
                            "informatica_registodocencia.estabelecimento_id = informatica_estabelecimento.id " +
                            "WHERE informatica_registodocencia.ano= " + year;
                dbcmd.CommandText = sql;
                IDataReader reader = dbcmd.ExecuteReader();

                while (reader.Read())
                {
                    string degreeName = reader.GetString(0);
                    list.Add(degreeName);
                }

                reader.Close();
                reader = null;
                list.Sort();
                finalList.Add("Year 200" + year + ": ", list);
            }

            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;

            return finalList;
        } //end of listEstabY



        /*
         * ###################################################################################
         * Function to get the list of olders of a degree by year
         * ###################################################################################
        */
        public Dictionary<string, List<string>> listHolDY()
        {
            Dictionary<string, List<string>> finalList = new Dictionary<string, List<string>>();

            string connectionString = "URI=file:database/pristine.db,version=3";
            IDbConnection dbcon;
            dbcon = (IDbConnection)new SqliteConnection(connectionString);
            dbcon.Open();
            IDbCommand dbcmd = dbcon.CreateCommand();

            for (int i = 0; i < 10; i++)
            {
                List<string> list = new List<string>();
                string year = i.ToString();
                string sql =
                            "SELECT DISTINCT informatica_docente.nome " +
                            "FROM informatica_registodocencia " +
                            "INNER JOIN informatica_docente on " +
                            "informatica_registodocencia.docente_id = informatica_docente.id " +
                            "WHERE informatica_registodocencia.ano= " + year;
                dbcmd.CommandText = sql;
                IDataReader reader = dbcmd.ExecuteReader();

                while (reader.Read())
                {
                    string teacherName = reader.GetString(0);
                    list.Add(teacherName);
                }

                reader.Close();
                reader = null;
                list.Sort();
                finalList.Add("Year 200" + year + ": ", list);
            }
            dbcmd.Dispose();
            dbcmd = null;
            dbcon.Close();
            dbcon = null;
            return finalList;
        } //end of listHolDY



        /*
         * ###########################################################
         * Function to get list winth the number of teachers 
         * leaving an establishment per year
         * ###########################################################
        */
        public Dictionary<string, Dictionary<string, int>> listTeachLeavEY()
        {
            Dictionary<string, Dictionary<string, int>> finalList = new Dictionary<string, Dictionary<string, int>>();

            for (int i = 0; i < 9; i++)
            {
                Console.WriteLine("Year 200{0} #######################", i+1);
                Dictionary<string, int> listTLeavEstY = new Dictionary<string, int>();
                Dictionary<string, int> sortedListTLeavEstY = new Dictionary<string, int>();
                Dictionary<string, int> list = sF.numberTeachEstY(i);
                Dictionary<string, int> listAfter = sF.numberTeachEstY(i+1);

                foreach (KeyValuePair<string, int> kvp in list)
                {
                    foreach (KeyValuePair<string, int> kvpAfter in listAfter)
                    {
                        if (kvp.Key == kvpAfter.Key)
                        {
                            if (kvp.Value > kvpAfter.Value)
                            {
                                int totLeaving = kvp.Value - kvpAfter.Value;
                                listTLeavEstY.Add(kvp.Key, totLeaving);
                            }
                        }
                    }
                }

                var items = from k in listTLeavEstY.Keys
                            orderby listTLeavEstY[k] descending
                            select k;

                foreach (string estab in items)
                {
                    sortedListTLeavEstY.Add(estab, listTLeavEstY[estab]);
                    Console.WriteLine(estab + ": " + listTLeavEstY[estab]);
                }

                finalList.Add("Year 200" + (i + 1), sortedListTLeavEstY);
                Console.WriteLine("Year 200{0} OK #######################", i+1);
                Console.WriteLine();
            }

            return finalList;
        } //end of listTechLeavEY


        /*
         * ###########################################################
         * Function to get list winth the number of new teachers 
         * per establishment and year
         * ###########################################################
        */
        public Dictionary<string, Dictionary<string, int>> listTeachNewEY()
        {

            Dictionary<string, Dictionary<string, int>> finalList = new Dictionary<string, Dictionary<string, int>>();

            for (int i = 1; i < 10; i++)
            {
                Console.WriteLine("Year 200{0} #######################", i);
                Dictionary<string, int> listTNewEstY = new Dictionary<string, int>();
                Dictionary<string, int> sortedListTNewEstY = new Dictionary<string, int>();
                Dictionary<string, int> listBefore = sF.numberTeachEstY(i-1);
                Dictionary<string, int> list = sF.numberTeachEstY(i);

                foreach (KeyValuePair<string, int> kvp in list)
                {
                    foreach (KeyValuePair<string, int> kvpBefore in listBefore)
                    {
                        if (kvp.Key == kvpBefore.Key)
                        {
                            if (kvp.Value > kvpBefore.Value)
                            {
                                int totNew = kvp.Value - kvpBefore.Value;
                                listTNewEstY.Add(kvp.Key, totNew);
                            }
                        }
                    }
                }

                var items = from k in listTNewEstY.Keys
                            orderby listTNewEstY[k] descending
                            select k;

                foreach (string estab in items)
                {
                    sortedListTNewEstY.Add(estab, listTNewEstY[estab]);
                    Console.WriteLine(estab + ": " + listTNewEstY[estab]);
                }

                finalList.Add("Year 200" + i, sortedListTNewEstY);

                Console.WriteLine("Year 200{0} OK #######################", i);
                Console.WriteLine();
            }

            return finalList;
        } //end of listTeachNewEY


        /*
         * ###########################################################
         * Function to get the statistic with the number of teachers 
         * that migrated from one establishment to another per year
         * ###########################################################
        */
        public Dictionary<string, int> statTeachMigratingY()
        {
            Dictionary<string, int> finalList = new Dictionary<string, int>();
            Dictionary<string, int> sortedList = new Dictionary<string, int>();


            for (int i = 0; i < 9; i++)
            {
                Console.WriteLine("Year 200{0} #######################", i + 1);
                List<MyDict> listAfter = sF.listTeachEstIdY(i + 1);
                List<MyDict> list = sF.listTeachEstIdY(i);
                var result = listAfter.Except(list);
                List<string> resultNames = new List<string>();
                List<string> listNames = new List<string>();
                List<string> changeListY = new List<string>();
                int percentCount = result.Count() / 10;

                foreach (MyDict name in list)
                {
                    listNames.Add(name.key);
                }

                foreach (MyDict name in result)
                {
                    resultNames.Add(name.key);
                }

                var result1 = resultNames.Intersect(listNames);

                foreach (string name in result1)
                {
                    changeListY.Add(name);
                }

                int total = changeListY.Count();

                sortedList.Add("Year 200" + (i + 1), total);
                Console.WriteLine("Year 200{0} OK #######################", i + 1);
                Console.WriteLine();
            }

            var items = from k in sortedList.Keys
                        orderby sortedList[k] ascending
                        select k;

            foreach (string k in items)
            {
                finalList.Add(k, sortedList[k]);
            }

            return finalList;
        } //end of statTeachMigratingY



        /*
         * ###########################################################
         * Function to get the statistic with the number of teachers 
         * promoted to the next category per establishment/year
         * ###########################################################
        */
        public Dictionary<string, Dictionary<string, int>> statTeachPromEstY()
        {
            Dictionary<string, Dictionary<string, int>> finalList = new Dictionary<string, Dictionary<string, int>>();
            
            


            for (int i = 0; i < 9; i++)
            {
                Console.WriteLine("Year 200{0} #######################", i + 1);
                Dictionary<string, List<MyDict>> dictAfter = sF.listTeachCatEtY(i + 1);
                Dictionary<string, List<MyDict>> dict = sF.listTeachCatEtY(i);
                Dictionary<string, int> finalMidleList = new Dictionary<string, int>();
                Dictionary<string, int> sortedList = new Dictionary<string, int>();

                foreach (KeyValuePair<string, List<MyDict>> dicAf in dictAfter)
                {
                    Console.WriteLine(dicAf.Key);
                    string n = dicAf.Key;
                    List<MyDict> listAfter = dicAf.Value;
                    if (dict.ContainsKey(dicAf.Key))
                    {
                        List<MyDict> list = dict[dicAf.Key];
                        if (dict[dicAf.Key] != null)
                        {
                            int percent = 0;
                            int pTimes = 1;
                            int percentCount = listAfter.Count() / 10;

                            List<MyDict> midleList = new List<MyDict>();

                            foreach (MyDict aDic in listAfter)
                            {
                                //int comp = 0;
                                percent += 1;
                                if (list.Contains(aDic))
                                {
                                    midleList.Add(aDic);
                                }
                                else if (list.Find(p => p.dKey == aDic.dKey) == null)
                                {
                                    midleList.Add(aDic);
                                }
                                if (percent == percentCount)
                                {
                                    Console.WriteLine(pTimes * 10 + "% processed");
                                    pTimes += 1;
                                    percent = 0;
                                }
                            }

                            var final = listAfter.Except(midleList);

                            int total = final.Count();

                            finalMidleList.Add(dicAf.Key, total);
                        }
                    }
                }

                var items = from k in finalMidleList.Keys
                            orderby finalMidleList[k] descending
                            select k;

                foreach (string k in items)
                {
                    sortedList.Add(k, finalMidleList[k]);
                }

                finalList.Add("Year 200" + (i + 1), sortedList);
                Console.WriteLine("Year 200{0} OK #######################", i + 1);
                Console.WriteLine();
            }

            return finalList;
        } //end of statTeachPromEstY

    } //end of class csRebides

} //end of namespace
